﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using DbHelper;
using Model;

namespace DAL
{
    public class GoodsInfoDal
    {
        public GoodsInfoDal()
        {
        }

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public List<MGoodsInfo> ListGoodsInfo(MGoodsInfo mgoods)
        {
            string sql = "select id,goodsId,classId,(select className from goodsClassfy where id=GoodsInfo.classId) as className,goodsName,sellerId,no,picSrc,leftNum,goodsUrl,addTime,lastUpdateTime,note from GoodsInfo where 1=1 ";
            List<MGoodsInfo> list = new List<MGoodsInfo>();
            if (mgoods.ClassId > 0)
            {
                sql += " and classId=@classId ";
            }
            if (!string.IsNullOrEmpty(mgoods.GoodsName))
            {
                sql += " and goodsName like '%" + mgoods.GoodsName + "%' ";
            }
            if (!string.IsNullOrEmpty(mgoods.No))
            {
                sql += " and no=@no ";
            }
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@classId",mgoods.ClassId),
                new SQLiteParameter("@no",mgoods.No)
            };
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql, sqliteParameter);
            while (sdr.Read())
            {
                MGoodsInfo mg = new MGoodsInfo();
                mg.Id = Convert.ToInt32(sdr["id"]);
                mg.GoodsId = Convert.ToString(sdr["goodsId"]);
                mg.ClassId = Convert.ToInt32(sdr["classId"]);
                mg.ClassName = Convert.ToString(sdr["className"]);
                mg.GoodsName = Convert.ToString(sdr["goodsName"]);
                mg.SellerId = Convert.ToInt32(sdr["sellerId"]);
                mg.No = Convert.ToString(sdr["no"]);
                mg.PicSrc = Convert.ToString(sdr["picSrc"]);
                mg.LeftNum = Convert.ToInt32(sdr["leftNum"]);
                mg.GoodsUrl = Convert.ToString(sdr["goodsUrl"]);
                mg.AddTime = Convert.ToDateTime(sdr["addTime"]);
                mg.LastUpdateTime = Convert.ToDateTime(sdr["lastUpdateTime"]);
                mg.Note = Convert.ToString(sdr["note"]);
                list.Add(mg);
            }
            return list;
        }

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="mgoods"></param>
        /// <param name="loginId"></param>
        /// <returns></returns>
        public List<MGoodsInfo> ListGoodsInfo(MGoodsInfo mgoods,int loginId)
        {
            string sql = "select g.id,goodsId,classId,(select className from goodsClassfy where id=g.classId) as className,goodsName,sellerId,no,picSrc,leftNum,goodsUrl,g.addTime,lastUpdateTime,note,(case (select 1 from MySubscribe where sellerid=@loginId and goodsid=g.goodsid) when 1 then 1 else 0 end ) as isinmygoods,aliww,shopname,shopurl from GoodsInfo g left join seller s on g.sellerId=s.id where 1=1 ";
            List<MGoodsInfo> list = new List<MGoodsInfo>();
            if (mgoods.ClassId > 0)
            {
                sql += " and classId=@classId ";
            }
            if (!string.IsNullOrEmpty(mgoods.GoodsName))
            {
                sql += " and goodsName like '%" + mgoods.GoodsName + "%' ";
            }
            if (!string.IsNullOrEmpty(mgoods.No))
            {
                sql += " and no=@no ";
            }
            if (!string.IsNullOrEmpty(mgoods.SellerInfo.Aliww))
            {
                sql += " and aliww=@aliww ";
            }
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@classId",mgoods.ClassId),
                new SQLiteParameter("@no",mgoods.No),
                new SQLiteParameter("@sellerId",mgoods.SellerId),
                new SQLiteParameter("@loginId",loginId),
                new SQLiteParameter("@aliww",mgoods.SellerInfo.Aliww)
            };
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql, sqliteParameter);
            while (sdr.Read())
            {
                MGoodsInfo mg = new MGoodsInfo();
                mg.Id = Convert.ToInt32(sdr["id"]);
                mg.GoodsId = Convert.ToString(sdr["goodsId"]);
                mg.ClassId = Convert.ToInt32(sdr["classId"]);
                mg.ClassName = Convert.ToString(sdr["className"]);
                mg.GoodsName = Convert.ToString(sdr["goodsName"]);
                mg.SellerId = Convert.ToInt32(sdr["sellerId"]);
                mg.No = Convert.ToString(sdr["no"]);
                mg.PicSrc = Convert.ToString(sdr["picSrc"]);
                mg.LeftNum = Convert.ToInt32(sdr["leftNum"]);
                mg.GoodsUrl = Convert.ToString(sdr["goodsUrl"]);
                mg.AddTime = Convert.ToDateTime(sdr["addTime"]);
                mg.LastUpdateTime = Convert.ToDateTime(sdr["lastUpdateTime"]);
                mg.Note = Convert.ToString(sdr["note"]);
                mg.IsInMyGoods = Convert.ToBoolean(sdr["isinmygoods"]);
                mg.SellerInfo.Aliww = Convert.ToString(sdr["aliww"] == DBNull.Value ? "" : sdr["aliww"]);
                mg.SellerInfo.ShopName = Convert.ToString(sdr["shopName"]==DBNull.Value ? "" : sdr["shopName"]);
                mg.SellerInfo.ShopUrl = Convert.ToString(sdr["shopUrl"]==DBNull.Value ? "" : sdr["shopUrl"]);
                list.Add(mg);
            }
            return list;
        }

        /// <summary>
        /// 新增数据
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public int Add(List<MGoodsInfo> list)
        {
            string sql = "insert into GoodsInfo(goodsName,sellerId,no,picSrc,leftNum,goodsUrl,addTime,lastUpdateTime,note)values(@goodsName,@sellerId,@no,@picSrc,@leftNum,@goodsUrl,datetime(),datetime(),@note)";
            int succCnt = 0;
            foreach (var li in list)
            {
                SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                    new SQLiteParameter("@goodsName",li.GoodsName),
                    new SQLiteParameter("@sellerId",li.SellerId),
                    new SQLiteParameter("@no",li.No),
                    new SQLiteParameter("@picSrc",li.PicSrc),
                    new SQLiteParameter("@leftNum",li.LeftNum),
                    new SQLiteParameter("@goodsUrl",li.GoodsUrl),
                    new SQLiteParameter("@note",li.Note),
                };
                succCnt = succCnt + DBHelper.ExecuteCommand(sql, sqliteParameter);
            }
            return succCnt;
        }

        public bool Save(List<MGoodsInfo> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    string sqlAdd = "insert into GoodsInfo(goodsId,goodsName,classId,sellerId,no,picSrc,leftNum,goodsUrl,addTime,lastUpdateTime,note)values(@goodsId,@goodsName,@classId,@sellerId,@no,@picSrc,@leftNum,@goodsUrl,datetime('now','localtime'),datetime('now','localtime'),@note)";
                    string sqlUpdate = "update GoodsInfo set goodsName=@goodsName,classId=@classId,sellerId=@sellerId,no=@no,picSrc=@picSrc,leftNum=@leftNum,goodsUrl=@goodsUrl,lastUpdateTime=datetime('now','localtime'),note=@note where goodsId=@goodsId";
                    string sqlDtl = "insert into goodsdtlinfo (goodsid,color,size,cnt,colorPicSrc) values (@goodsid,@color,@size,0,@colorPicSrc)";
                    SQLiteParameter[] sqliteParameter;
                    foreach (var li in list)
                    {
                        sqliteParameter = new SQLiteParameter[]{
                            new SQLiteParameter("@goodsId",li.GoodsId),
                            new SQLiteParameter("@goodsName",li.GoodsName),
                            new SQLiteParameter("@classId",li.ClassId),
                            new SQLiteParameter("@sellerId",li.SellerId),
                            new SQLiteParameter("@no",li.No),
                            new SQLiteParameter("@picSrc",li.PicSrc),
                            new SQLiteParameter("@leftNum",li.LeftNum),
                            new SQLiteParameter("@goodsUrl",li.GoodsUrl),
                            new SQLiteParameter("@note",li.Note),
                            new SQLiteParameter("@id",li.Id)
                        };
                        if (DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sqlUpdate, sqliteParameter) <= 0)//更新数量0，表明不存在该商品，所以需要插入
                        {
                            DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sqlAdd, sqliteParameter);
                            foreach (var dtl in li.ListGoodsDtlInfo)
                            {
                                SQLiteParameter[] sqliteDtlParameter = new SQLiteParameter[]{
                                    new SQLiteParameter("@goodsid",dtl.GoodsId),
                                    new SQLiteParameter("@color",dtl.Color),
                                    new SQLiteParameter("@size",dtl.Size)
                                };
                                DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sqlDtl, sqliteDtlParameter);
                            }
                        }
                        else//修改商品信息的时候
                        {
                            string sql = "select 1 from goodsdtlinfo where goodsid=@goodsid";
                            SQLiteParameter[] sqliteDtlParameter = new SQLiteParameter[]{
                                new SQLiteParameter("@goodsid",li.GoodsId),
                            };
                            if (DbHelperSQLite.Query(sql, sqliteDtlParameter).Tables[0].Rows.Count <= 0 && li.ListGoodsDtlInfo != null)//判断是否存在详细信息，如果不存在，则添加
                            {
                                foreach (var dtl in li.ListGoodsDtlInfo)
                                {
                                    sqliteDtlParameter = new SQLiteParameter[]{
                                        new SQLiteParameter("@goodsid",dtl.GoodsId),
                                        new SQLiteParameter("@color",dtl.Color),
                                        new SQLiteParameter("@size",dtl.Size),
                                        new SQLiteParameter("@colorPicSrc",dtl.ColorPicSrc)
                                    };
                                    DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sqlDtl, sqliteDtlParameter);
                                }
                            }
                        }
                    }
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public int Update(List<MGoodsInfo> list)
        {
            string sql = "update GoodsInfo set goodsName=@goodsName,sellerId=@sellerId,no=@no,picSrc=@picSrc,leftNum=@leftNum,goodsUrl=@goodsUrl,lastUpdateTime=datetime(),note=@note where id=@id";
            SQLiteParameter[] sqliteParameter = null;
            foreach (var li in list)
            {
                sqliteParameter = new SQLiteParameter[]{
                    new SQLiteParameter("@goodsName",li.GoodsName),
                    new SQLiteParameter("@sellerId",li.SellerId),
                    new SQLiteParameter("@no",li.No),
                    new SQLiteParameter("@picSrc",li.PicSrc),
                    new SQLiteParameter("@leftNum",li.LeftNum),
                    new SQLiteParameter("@goodsUrl",li.GoodsUrl),
                    new SQLiteParameter("@note",li.Note),
                    new SQLiteParameter("@id",li.Id)
                };
            }
            return DBHelper.ExecuteCommand(sql, sqliteParameter);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public int Del(string ids, int sellerId)
        {
            string sql = "delete from GoodsInfo where id in (" + ids + ") and sellerId=@sellerId";
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@sellerId",sellerId),
            };
            return DBHelper.ExecuteCommand(sql, sqliteParameter);
        }

        /// <summary>
        /// 根据商品id获得商品明细信息
        /// </summary>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public List<GoodsDtlInfo> ListGoodsDtlInfo(string goodsId)
        {
            string sql = "select id,goodsid,color,size,cnt,colorpicsrc from goodsdtlinfo where goodsid=@goodsid";
            SQLiteParameter[] sqliteParameter = new SQLiteParameter[]{
                new SQLiteParameter("@goodsid", goodsId),
            };
            List<GoodsDtlInfo> list = new List<GoodsDtlInfo>();
            SQLiteDataReader sdr = DbHelperSQLite.ExecuteReader(sql, sqliteParameter);
            while (sdr.Read())
            {
                GoodsDtlInfo model = new GoodsDtlInfo();
                model.Id = Convert.ToInt32(sdr["id"]);
                model.GoodsId = goodsId;
                model.Color = Convert.ToString(sdr["color"]);
                model.Size = Convert.ToString(sdr["size"]);
                model.Cnt = Convert.ToInt32(sdr["cnt"] == DBNull.Value ? "0" : sdr["cnt"].ToString());
                model.ColorPicSrc = Convert.ToString(sdr["colorpicsrc"]);
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// 保存明细数据
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool SaveDtl(List<GoodsDtlInfo> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(DbHelperSQLite.connectionString))
            {
                conn.Open();
                SQLiteTransaction trans = conn.BeginTransaction();
                try
                {
                    SQLiteParameter[] sqliteParameter;
                    string sql = "update goodsdtlinfo set cnt=@cnt where id=@id";
                    foreach (var li in list)
                    {
                        sqliteParameter = new SQLiteParameter[]{
                            new SQLiteParameter("@cnt", li.Cnt),
                            new SQLiteParameter("@id", li.Id)
                        };
                        DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    }

                    sql = "update goodsinfo set leftNum=(select sum(cnt) from goodsdtlinfo where goodsid=@goodsid) where goodsid=@goodsid";//更新主表相应的数量字段
                    sqliteParameter = new SQLiteParameter[]{
                        new SQLiteParameter("@goodsid", list[0].GoodsId)
                    };
                    DbHelperSQLite.ExecuteSql(trans, CommandType.Text, sql, sqliteParameter);
                    trans.Commit();
                    return true;
                }
                catch
                {
                    trans.Rollback();
                    return false;
                }
            }
        }
    }
}
